package sch.frog.utils;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Excel工具类
 * 
 * 相关pom文件:
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>3.13</version>
	</dependency>
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml</artifactId>
		<version>3.13</version>
	</dependency>
 */
public class ExcelUtil {

	public enum WorkbookType {
		XLS, XLSX;

		@Override
		public String toString() {
			return super.toString().toLowerCase();
		}
	}

	private static final DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

	/**
	 * 读取Excel中的数据
	 * 
	 * @param inputStream
	 * @return	ArrayList 泛型 LinkedHashMap
	 * @throws IOException
	 */
	public static List<Map<String, String>> getExcelData(InputStream inputStream) throws IOException {
		return getExcelData(inputStream, 0);
	}

	/**
	 * 读取excel中的数据
	 * @param inputStream
	 * @param sheetName	sheet的名字
	 * @return	ArrayList 泛型 LinkedHashMap
	 * @throws IOException
	 */
	public static List<Map<String, String>> getExcelData(InputStream inputStream, String sheetName) throws IOException {
		List<Map<String, String>> tableData = null;
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(inputStream);
			Sheet sheet = wb.getSheet(sheetName);
			if(sheet == null) return null;
			
			tableData = getExcelData(sheet);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (wb != null) {
				wb.close();
			}
		}
		return tableData;
	}

	/**
	 * 读取excel中的数据
	 * 
	 * @param inputStream
	 * @param sheetIndex  第几个sheet, 从0开始
	 * @return	ArrayList 泛型 LinkedHashMap
	 * @throws IOException
	 */
	public static List<Map<String, String>> getExcelData(InputStream inputStream, int sheetIndex) throws IOException {
		List<Map<String, String>> tableData = null;
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(inputStream);
			int sheetCount = wb.getNumberOfSheets();
			if (sheetIndex >= sheetCount) return null;
			
			Sheet sheet = wb.getSheetAt(sheetIndex);

			tableData = getExcelData(sheet);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (wb != null) {
				wb.close();
			}
		}
		return tableData;
	}
	
	/**
	 * 获取excel中所有行
	 * @param inputStream
	 * @param sheetIndex
	 * @return
	 */
	public static List<String[]> getRowList(InputStream inputStream, int sheetIndex){
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(inputStream);
			int sheetCount = wb.getNumberOfSheets();
			if (sheetIndex >= sheetCount) return null;
			
			Sheet sheet = wb.getSheetAt(sheetIndex);

			return getRowListWithSheet(sheet);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (wb != null) {
				try {
					wb.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}
	
	private static List<String[]> getRowListWithSheet(Sheet sheet){
		List<String[]> list = new ArrayList<>();
		int rowCount = sheet.getPhysicalNumberOfRows();
		
		for(int i = 0; i < rowCount; i++) {
			Row row = sheet.getRow(i);
			if(row == null) continue;
			
			int ofCells = row.getPhysicalNumberOfCells();
			String[] rowData = new String[ofCells];
			for(int j = 0; j < ofCells; j++) {
				Cell cell = row.getCell(j);
				if(cell == null) {
					rowData[j] = null;
				}else {
					rowData[j] = getCellValue(cell);
				}
			}
			
			list.add(rowData);
		}
		
		return list;
	}
	
	/**
	 * 读取Excel中所有行
	 * @param inputStream
	 * @param sheetIndex
	 * @param startCol
	 * @param endCol
	 * @return
	 */
	public static List<String[]> getRowList(InputStream inputStream, int sheetIndex, int startCol, int endCol){
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(inputStream);
			int sheetCount = wb.getNumberOfSheets();
			if (sheetIndex >= sheetCount) return null;
			
			Sheet sheet = wb.getSheetAt(sheetIndex);

			return getRowListWithSheet(sheet, startCol, endCol);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (wb != null) {
				try {
					wb.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}

	
	private static List<String[]> getRowListWithSheet(Sheet sheet, int startCol, int endCol){
		List<String[]> list = new ArrayList<>();
		int rowCount = sheet.getPhysicalNumberOfRows();
		
		int colCount = endCol - startCol;
		if(colCount < 0) {
			throw new IndexOutOfBoundsException();
		}
		
		for(int i = 0; i < rowCount; i++) {
			Row row = sheet.getRow(i);
			if(row == null) continue;
			
			int ofCells = row.getPhysicalNumberOfCells();
			String[] rowData = new String[colCount];
			int endColIndex = ofCells >= endCol + 1 ? endCol : (ofCells - 1);
			for(int j = startCol; j <= endColIndex; j++) {
				Cell cell = row.getCell(j);
				if(cell == null) {
					rowData[j - startCol] = null;
				}else {
					rowData[j - startCol] = getCellValue(cell);
				}
			}
			
			list.add(rowData);
		}
		
		return list;
	}

	private static List<Map<String, String>> getExcelData(Sheet sheet) throws IOException {
		List<Map<String, String>> tableData = new ArrayList<Map<String, String>>();
		

		int rowCount = sheet.getPhysicalNumberOfRows();
		Row firstRow = sheet.getRow(0);
		int columnCount = firstRow.getPhysicalNumberOfCells();
		List<String> titleList = new ArrayList<String>();
		for (int i = 0; i < columnCount; i++) {
			Cell cell1 = firstRow.getCell(i);
			titleList.add(cell1.getStringCellValue().trim());
		}

		for (int rowIndex = 1; rowIndex < rowCount; rowIndex++) {
			Row row = sheet.getRow(rowIndex);
			if (row == null)
				continue;
			Map<String, String> rowData = new LinkedHashMap<String, String>();
			for (int columnIndex = 0; columnIndex < columnCount; columnIndex++) {
				Cell cell = row.getCell(columnIndex);
				if (cell == null) {
					rowData.put(titleList.get(columnIndex), "");
					continue;
				}
				String cellValue = getCellValue(cell);
				rowData.put(titleList.get(columnIndex), cellValue.trim());
			}
			tableData.add(rowData);
		}

		return tableData;
	}
	
	private static String getCellValue(Cell cell) {
		int cellType = cell.getCellType();
		String cellValue = "";
		switch (cellType) {
		case Cell.CELL_TYPE_STRING: // 文本
			cellValue = cell.getStringCellValue() != null ? cell.getStringCellValue() : "";
			break;
		case Cell.CELL_TYPE_NUMERIC: // 数字、日期
			if (DateUtil.isCellDateFormatted(cell)) {
				if (cell.getDateCellValue() != null) {
					cellValue = dateFormat.format(cell.getDateCellValue()); // 日期型
				} else {
					cellValue = "";
				}
			} else {
				cellValue = String.valueOf(cell.getNumericCellValue()); // 数字
			}
			break;
		case Cell.CELL_TYPE_BOOLEAN: // 布尔型
			cellValue = String.valueOf(cell.getBooleanCellValue());
			break;
		case Cell.CELL_TYPE_BLANK: // 空白
			cellValue = "";
			break;
		case Cell.CELL_TYPE_ERROR: // 错误
			cellValue = "error";
			break;
		case Cell.CELL_TYPE_FORMULA: // 公式
			cellValue = "error";
			break;
		default:
			cellValue = "error";
			break;
		}
		return cellValue;
	}

	/**
	 * 导出Excel
	 * 
	 * @param sheetName
	 * @param title
	 * @param values
	 * @param type
	 * @return
	 * @throws IOException 
	 */
	public static void exportExcel(String sheetName, String[] title, List<String[]> values, WorkbookType type, OutputStream outputStream) throws IOException {
		
		// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
		Workbook wb = null;
		switch (type) {
		case XLS:
			wb = new HSSFWorkbook();
			break;
		case XLSX:
			wb = new XSSFWorkbook();
			break;
		default:
			break;
		}

		// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
		Sheet sheet = null;
		if (sheetName != null && !"".equals(sheetName.trim())) {
			sheet = wb.createSheet(sheetName);
		} else {
			sheet = wb.createSheet("Sheet1");
		}

		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
		Row row = sheet.createRow(0);

		// 第四步，创建单元格，并设置值表头 设置表头居中
		CellStyle style = wb.createCellStyle();
		style.setAlignment(CellStyle.ALIGN_CENTER); // 创建一个居中格式

		// 声明列对象
		Cell cell = null;

		// 创建标题
		for (int i = 0; i < title.length; i++) {
			cell = row.createCell(i);
			cell.setCellValue(title[i]);
			cell.setCellStyle(style);
		}

		CellStyle dateStyle = wb.createCellStyle();
		dateStyle.setDataFormat((short) BuiltinFormats.getBuiltinFormat("m/d/yy"));
		// 创建内容
		for (int i = 0; i < values.size(); i++) {
			row = sheet.createRow(i + 1);
			for (int j = 0; j < values.get(i).length; j++) {
				String value = values.get(i)[j];
				Cell contentCell;
				int cellType = cellType(value);
				switch (cellType) {
				case NUM:
					contentCell = row.createCell(j, Cell.CELL_TYPE_NUMERIC);
					contentCell.setCellValue(Float.parseFloat(value));
					break;
				case DATE:
					contentCell = row.createCell(j);
					try {
						contentCell.setCellStyle(dateStyle);
						contentCell.setCellValue(dateFormat.parse(value));
					} catch (ParseException e) {
						contentCell.setCellValue(value);
					}
					break;
				case BLANK:
					contentCell = row.createCell(j, Cell.CELL_TYPE_BLANK);
					contentCell.setCellValue("");
					break;
				default:
					contentCell = row.createCell(j);
					contentCell.setCellValue(value);
					break;
				}
			}
		}

		wb.write(outputStream);
		wb.close();
	}
	
	/**
	 * 导出Excel
	 * @param sheetName
	 * @param tableData
	 * @param type
	 * @return
	 * @throws IOException 
	 */
	public static void exportExcel(String sheetName, List<Map<String, String>> tableData, WorkbookType type, OutputStream outputStream) throws IOException {
		Set<String> titles = new LinkedHashSet<>();

		for (Map<String, String> map : tableData) {
			Set<String> keySet = map.keySet();
			for (String key : keySet) {
				titles.add(key);
			}
		}

		List<String[]> values = new ArrayList<String[]>();
		String[] titleArr = titles.toArray(new String[titles.size()]);
		
		for (Map<String, String> map : tableData) {
			String[] content = new String[titleArr.length];
			for (int i = 0; i < titleArr.length; i++) {
				content[i] = map.get(titleArr[i]);
			}
			values.add(content);
		}

		exportExcel(sheetName, titleArr, values, type, outputStream);
	}

	private static final int NUM = 0;
	private static final int DATE = 1;
	private static final int TEXT = 2;
	private static final int BLANK = 3;

	private static int cellType(String value) {
		String numberRegex = "^[0-9]+.?[0-9]*$";
		String dateRegex = "^[1-9]\\d{3}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[0-1])$";
		if (value == null || "".equals(value.trim())) {
			return BLANK;
		} else if (value.matches(numberRegex)) {
			return NUM;
		} else if (value.matches(dateRegex)) {
			return DATE;
		} else {
			return TEXT;
		}
	}

}
